﻿<###################################################################################################################################
 # Name:	WaitStatistics.ps1
 # Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 # Summary:	Collect wait statistics from a list of servers.
 #
 # PowerShell.exe -NoProfile -NonInteractive -Command "& 'C:\DBAdmin\SQL Waits\WaitStatistics.ps1'"
 #
 # $ErrorActionPreference= "SilentlyContinue";
 # $VerbosePreference= "Continue";
 # $ServerListFile= "C:\Users\EZVHNL\Documents\T-SQL Scripts\DBAdmin ToolKit\PoSh\SQL Waits\Servers.txt";
 #>

$WarningLogFile= "$PSScriptRoot\WaitStatistics_Warnings.txt";
$ErrorLogFile  = "$PSScriptRoot\WaitStatistics_Errors.txt";
$ServerListFile= "$PSScriptRoot\Servers.txt";

$Warning= $null;
Get-Date | Out-File $WarningLogFile;

$Error.Clear();
Get-Date | Out-File $ErrorLogFile;

Write-Verbose $ServerListFile;

$cn= New-Object System.Data.SqlClient.SQLConnection("Data Source=BBA-Phl-vDBAdmin.BBAAviation.net;Initial Catalog=DBAdmin;Integrated Security=SSPI;");
$cn.Open();

$cmd_InsertStats= New-Object System.Data.SqlClient.SqlCommand;
$cmd_InsertStats.CommandText= "INSERT INTO dbo.SQLWaitStats (server_name, server_time, wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms) VALUES (@server_name, @server_time, @wait_type, @waiting_tasks_count, @wait_time_ms, @max_wait_time_ms, @signal_wait_time_ms);";
[void]$cmd_InsertStats.Parameters.Add("@server_name"		, "NVarChar"		,   128, "server_name");
[void]$cmd_InsertStats.Parameters.Add("@server_time"		, "DateTimeOffset"	, $null, "server_time");
[void]$cmd_InsertStats.Parameters.Add("@wait_type"			, "NVarChar"		,    60, "wait_type");
[void]$cmd_InsertStats.Parameters.Add("@waiting_tasks_count", "BigInt"			, $null, "waiting_tasks_count");
[void]$cmd_InsertStats.Parameters.Add("@wait_time_ms"		, "BigInt"			, $null, "wait_time_ms");
[void]$cmd_InsertStats.Parameters.Add("@max_wait_time_ms"	, "BigInt"			, $null, "max_wait_time_ms");
[void]$cmd_InsertStats.Parameters.Add("@signal_wait_time_ms", "BigInt"			, $null, "signal_wait_time_ms");
$cmd_InsertStats.Connection= $cn;

foreach ($ServerName in (Get-Content $ServerListFile)) {
    Write-Verbose $ServerName;
    $da_Get= New-Object System.Data.SqlClient.SqlDataAdapter("SELECT * FROM DBAdmin.ShowWaitStats;", "Data Source=$ServerName;Initial Catalog=master;Integrated Security=SSPI;")
    $ds_Get= New-Object System.Data.DataSet;
    [void]$da_Get.Fill($ds_Get);

    # sys.dm_os_wait_stats
    foreach ($r in $ds_Get.Tables[0]) {
        $cmd_InsertStats.Parameters[ "@server_name"         ].Value= $r.server_name;
        $cmd_InsertStats.Parameters[ "@server_time"         ].Value= $r.server_time;
        $cmd_InsertStats.Parameters[ "@wait_type"           ].Value= $r.wait_type;
        $cmd_InsertStats.Parameters[ "@waiting_tasks_count" ].Value= $r.waiting_tasks_count;
        $cmd_InsertStats.Parameters[ "@wait_time_ms"        ].Value= $r.wait_time_ms;
        $cmd_InsertStats.Parameters[ "@max_wait_time_ms"    ].Value= $r.max_wait_time_ms;
        $cmd_InsertStats.Parameters[ "@signal_wait_time_ms" ].Value= $r.signal_wait_time_ms;
        [void]$cmd_InsertStats.ExecuteNonQuery();
    };

    Out-File -FilePath $WarningLogFile -Append -InputObject $Warning;
    Out-File -FilePath $ErrorLogFile   -Append -InputObject $Error;

    $ds_Get.Dispose();
    $da_Get.Dispose();
};
$cn.Close();
Out-File -FilePath $WarningLogFile -Append -InputObject $Warning;
Out-File -FilePath $ErrorLogFile   -Append -InputObject $Error;